package com.studentmanage.manage_score.dao;

import com.studentmanage.framework.domain.base.ScoreExt;
import com.studentmanage.framework.domain.base.ScoreExt2;
import com.studentmanage.framework.domain.base.request.ScoreQueryRequest;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface ScoreExtMapper {
    String QUERY_CODE_SQL = " <if test=\"scoreQueryRequest.year != null and scoreQueryRequest.year != ''\"> " +
            "and score.year = #{scoreQueryRequest.year}" +
            "</if> " +
            "<if test=\"scoreQueryRequest.term != null and scoreQueryRequest.term != ''\"> " +
            "and score.term = #{scoreQueryRequest.term} " +
            "</if> ";
    String QUERY_CODE_SQL2 = " <if test=\"scoreQueryRequest.studentId != null and scoreQueryRequest.studentId != ''\"> " +
            "and student.id like \"%\"#{scoreQueryRequest.studentId}\"%\" " +
            "</if> " +
            "<if test=\"scoreQueryRequest.studentName != null and scoreQueryRequest.studentName != ''\"> " +
            "and student.name like \"%\"#{scoreQueryRequest.studentName}\"%\" " +
            "</if> ";

    //查询所有成绩列表
    @Select({"<script>select student.id, student.name as studentName, course.name as courseName, course.nature, score.finalpoint, score.finalscore," +
            "course.teachername, course.kind," +
            "college.name as collegeName"+
            "from score, student, class, course, college" +
            "where score.studentid = student.id" +
            "and score.courseid = course.id" +
            "and course.collegeid = college.id" +
            "and student.classid = class.id</script>"})
    public List<ScoreExt> findList(int page, int size);

    //查询某个学生的成绩列表
    @Select({"<script>select student.id as studentId, student.name, score.id, score.year, score.term, course.name as courseName, score.finalpoint, score.finalscore," +
            "course.teachername, course.kind, course.nature, " +
            "score.passtag, score.rebuildtag, "+
            "college.name as collegeName, course.id as courseId "+
            "from score, student, course, college where score.studentid = student.id " +
            "and college.id = course.collegeid "+
            "and score.courseid = course.id " +
            "and student.id = #{id} "+QUERY_CODE_SQL+" limit #{scoreQueryRequest.page}, #{scoreQueryRequest.size}</script>"})
    public List<ScoreExt> findListByStudent(String id, @Param("scoreQueryRequest")ScoreQueryRequest scoreQueryRequest);
    //查询某个学生的成绩列表总数
    @Select({"<script>select count(*) "+
            "from score, student, course, college where score.studentid = student.id " +
            "and college.id = course.collegeid "+
            "and score.courseid = course.id " +
            "and student.id = #{id} "+QUERY_CODE_SQL+"</script>"})
    public Integer getTotalByStudent(String id, @Param("scoreQueryRequest")ScoreQueryRequest scoreQueryRequest);

    //查询某个课程的成绩列表
    @Select({"<script>select score.id, course.id as courseId, student.name, score.year, score.term, course.teachername, finalpoint, finalscore, passtag, rebuildtag, student.id as studentId " +
            "from student, score, course  " +
            "where course.id = score.courseid  " +
            "and score.studentid = student.id  " +
            "and course.id = #{id} "+QUERY_CODE_SQL2+" limit #{scoreQueryRequest.page}, #{scoreQueryRequest.size}</script>"})
    public List<ScoreExt2> findListByCourse(String id, @Param("scoreQueryRequest")ScoreQueryRequest scoreQueryRequest);
    //查询某个课程的成绩列表总数
    @Select({"<script>select count(*) " +
            "from student, score, course " +
            "where course.id = score.courseid " +
            "and score.studentid = student.id " +
            "and course.id = #{id} "+QUERY_CODE_SQL2+"</script>"})
    public Integer getTotalByCourse(String id, @Param("scoreQueryRequest")ScoreQueryRequest scoreQueryRequest);

    @Select({"<script>select student.name, score.year, score.term, course.name as courseName, score.finalpoint, score.finalscore," +
            "course.teachername, course.kind, course.nature, " +
            "score.passtag, score.rebuildtag, "+
            "college.name as collegeName "+
            "from score, student, course, college where score.studentid = student.id " +
            "and college.id = course.collegeid "+
            "and score.courseid = course.id " +
            "and student.id = #{id} </script>"})
    public List<ScoreExt> findExclList(String id);
}
